# simulate_score.py
# usage:
# python ./src/tools/simulate_score.py > tmp_score.1505.log

import os,sys
project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
sys.path.insert(0, project_root)
import json
import sqlite3
from pathlib import Path
from config import project_config

# 路径配置
LOG_FILE = project_config.check_data_path 
QUESTION_JSONL = project_config.requestions_path
SAMPLES_FILE = project_config.samples_path 
RESULTS_FILE = project_config.data_answer_path 
DB_FILE = project_config.db_sqlite_url

def load_jsonl(file_path):
    """读取 JSONL 文件，返回字典列表"""
    data = []
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            for line_num, line in enumerate(f, 1):
                line = line.strip()
                if not line:
                    continue
                try:
                    data.append(json.loads(line))
                except json.JSONDecodeError as e:
                    msg = f"[警告] {file_path} 第 {line_num} 行 JSON 解析失败: {e}"
                    print(msg)
                    log_write(msg)
    except Exception as e:
        msg = f"[错误] 无法读取文件 {file_path}: {e}"
        print(msg)
        log_write(msg)
    return data

def log_write(message):
    """将消息写入日志文件"""
    try:
        Path(LOG_FILE).parent.mkdir(parents=True, exist_ok=True)
        with open(LOG_FILE, 'a', encoding='utf-8') as f:
            f.write(message + '\n')
    except Exception as e:
        print(f"[日志写入失败] {e}")

def save_to_question_jsonl(sample):
    """将 sample 记录追加写入 ./data/requestions.jsonl"""
    try:
        Path(QUESTION_JSONL).parent.mkdir(parents=True, exist_ok=True)
        with open(QUESTION_JSONL, 'a', encoding='utf-8') as f:
            f.write(json.dumps(sample, ensure_ascii=False) + '\n')
    except Exception as e:
        print(f"[写入失败] 无法将 sample 写入 {QUESTION_JSONL}: {e}")

def execute_sql(cursor, sql):
    """执行 SQL 并返回排序后的结果（按第一列）"""
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        # 按第一列排序，None 排在前面
        sorted_results = sorted(results, key=lambda x: (x[0] is None, x[0]))
        return sorted_results, None
    except sqlite3.Error as e:
        error_msg = f"SQL Error [{e.args[0]}]: {e}"
        return None, error_msg
    except Exception as e:
        error_msg = f"Unexpected Error: {e}"
        return None, error_msg

def format_result_display(results):
    """格式化结果显示：前3条 + 总数"""
    if not results:
        return "[] (共 0 条)"
    total = len(results)
    if total <= 3:
        items_str = str(results)
        return f"{items_str} (共 {total} 条)"
    else:
        first_three = results[:3]
        return f"{first_three}... 一共{total}笔记录"

def compare_results(id_, question, sql_sample, sql_result, cursor, sample_data):
    """
    执行两个 SQL 并比对结果。
    不一致或任一执行失败时：
        - 输出详细信息到日志
        - 将 sample_data 写入 ./data/question.jsonl
    """
    result_sample, err_sample = execute_sql(cursor, sql_sample)
    result_result, err_result = execute_sql(cursor, sql_result)

    lines = []
    status = False  # 默认失败

    sample_ok = result_sample is not None
    result_ok = result_result is not None

    if err_sample:
        lines.append(f"[Sample SQL Error] {err_sample}")
    else:
        disp_sample = format_result_display(result_sample)
        # lines.append(f"Sample Result: {disp_sample}")
        sample_ok = True

    if err_result:
        lines.append(f"[Result SQL Error] {err_result}")
    else:
        disp_result = format_result_display(result_result)
        # lines.append(f"Result Result: {disp_result}")
        result_ok = True

    # 比对逻辑
    if sample_ok and result_ok:
        if result_sample == result_result:
            status = True
        else:
            # ❌ 不一致
            lines.append(f"\nID: {id_} - Question: {question}")
            lines.append(f"Sample Result: {format_result_display(result_sample)}")
            lines.append(f"Result Result: {format_result_display(result_result)}")
            lines.extend([
                f"Sample SQL: {sql_sample}",
                f"Result SQL: {sql_result}",
                "Status: ❌ 不一致"
            ])
            save_to_question_jsonl(sample_data)
    else:
        # ⚠️ 执行出错
        lines.append(f"\nID: {id_} - Question: {question}")
        if err_sample:
            lines.append(f"[Sample Error] {err_sample}")
            lines.append(f"Sample SQL: {sql_sample}")
        if err_result:
            lines.append(f"[Result Error] {err_result}")
            lines.append(f"Result SQL: {sql_result}")
        lines.append("Status: ⚠️ SQL 执行出错（已跳过比对）")
        save_to_question_jsonl(sample_data)

    # 输出日志
    for line in lines:
        print(line)
        log_write(line)

    return status

def main():
    # 初始化日志
    try:
        Path(LOG_FILE).parent.mkdir(parents=True, exist_ok=True)
        with open(LOG_FILE, 'w', encoding='utf-8') as f:
            f.write("🔍 SQL 结果比对日志（仅扫描 results.jsonl）\n")
            f.write("="*60 + "\n")
    except Exception as e:
        print(f"[⚠️] 无法初始化日志文件 {LOG_FILE}: {e}")

    # 检查必要文件
    for path in [SAMPLES_FILE, RESULTS_FILE, DB_FILE]:
        if not Path(path).exists():
            msg = f"❌ 文件不存在: {path}"
            print(msg)
            log_write(msg)
            return

    # 加载数据
    samples = load_jsonl(SAMPLES_FILE)
    results = load_jsonl(RESULTS_FILE)

    # 构建 samples 字典，便于快速查找
    samples_dict = {item['id']: item for item in samples}

    # 连接数据库
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
    except sqlite3.Error as e:
        msg = f"❌ 无法连接数据库 {DB_FILE}: {e}"
        print(msg)
        log_write(msg)
        return

    total_count = 0
    match_count = 0

    header = "🔍 开始比对 SQL 查询结果（仅遍历 results.jsonl）...\n" + "-" * 80
    print(header)
    log_write(header)

    # 核心逻辑：只遍历 results.jsonl
    for item_result in results:
        total_count += 1
        id_ = item_result['id']
        try:
            # 获取对应的 sample
            item_sample = samples_dict.get(id_)

            if item_sample is None:
                # ❌ 找不到对应 sample
                question = item_result.get('question', 'Unknown')
                msg = f"\nID: {id_}\n[⚠️] 在 samples.jsonl 中未找到对应样本"
                print(msg)
                log_write(msg)
                # 构造一个最小 sample 用于保存
                fallback_sample = {
                    "id": id_,
                    "question": question,
                    "sql": item_result.get("sql", ""),
                    "note": "[Auto-generated] Missing in samples.jsonl"
                }
                save_to_question_jsonl(fallback_sample)
                continue

            # 提取字段
            question = item_sample.get('question', 'N/A')
            sql_sample = item_sample['sql'].strip()
            sql_result = item_result['sql'].strip()

            # 执行比对
            is_match = compare_results(id_, question, sql_sample, sql_result, cursor, item_sample)
            if is_match:
                match_count += 1

        except Exception as e:
            msg = f"\nID: {id_}\n[❌ 未知错误] {e}"
            print(msg)
            log_write(msg)
            # 出错也尝试保存 sample
            if id_ in samples_dict:
                save_to_question_jsonl(samples_dict[id_])
            continue

    conn.close()

    # 汇总
    summary = (
        "\n" + "=" * 80 +
        f"\n✅ 总共处理 results 数量: {total_count}" +
        f"\n✅ 一致的数量: {match_count}" +
        f"\n📊 准确率: {match_count/total_count*100:.2f}%" +
        "\n" + "=" * 80
    )
    print(summary)
    log_write(summary)

if __name__ == "__main__":
    main()